Payments Fraud Detection

Payments Fraud
Detection

SQLORACLEFRAUD ANALYSIS6.3M ROWS

This project analyses a dataset of over 6.3 million mobile-money payment transactions to detect and understand fraudulent activity using Oracle SQL. Starting from a raw CSV loaded via an external table, the analysis works through data quality checks, then investigates where fraud happens, how much money is involved, when it occurs, and how effective the existing fraud-detection flag is.

Key Findings

Fraud is extremely rare — only 0.012% of all transactions were fraudulent.
Fraud is concentrated in just two transaction types: CASH_OUT and TRANSFER. PAYMENT, DEBIT and CASH_IN had zero fraud.
The average fraudulent transaction (£1,467,967) is roughly 8× larger than the average legitimate one (£178,197).
Fraud almost always drains the account to zero — 8,053 of 8,213 fraud cases (98%) emptied the sender's balance.
The platform's existing fraud flag caught almost none of the real frauds — a major detection gap.

01 — Data Setup & Quality

The raw CSV is exposed through an Oracle external table, then materialised into a physical table for faster querying. Before any analysis, the data is checked for volume, the overall fraud rate, transaction mix, and duplicates.

sql

CREATE TABLE payment_transactions_ext
(
    step             NUMBER,
    txn_type         VARCHAR2(20),
    amount           NUMBER(15, 2),
    name_orig        VARCHAR2(50),
    oldbalance_org   NUMBER(15, 2),
    newbalance_orig  NUMBER(15, 2),
    name_dest        VARCHAR2(50),
    oldbalance_dest  NUMBER(15, 2),
    newbalance_dest  NUMBER(15, 2),
    is_fraud         NUMBER(1),
    is_flagged_fraud NUMBER(1)
) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_dir
    ACCESS PARAMETERS ( RECORDS DELIMITED BY '\n'
    SKIP 1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' )
    LOCATION ('fraud_detection.csv') )
    REJECT LIMIT UNLIMITED;

-- Materialise into a physical table for performance
CREATE TABLE payments_transactions AS
SELECT * FROM payment_transactions_ext;

sql

-- Total transactions
SELECT count(*) AS Total_Transactions
FROM payments_transactions;

-- Percentage of fraudulent transactions  (0.012%)
SELECT ROUND(
  (SELECT count(*) FROM payments_transactions WHERE is_fraud <> 0) /
  (SELECT count(*) FROM payments_transactions), 5) AS Fraud_Percentage
FROM dual;

sql

-- How many transactions per type
SELECT txn_type, count(txn_type) AS Nr_of_transactions
FROM payments_transactions
GROUP BY txn_type
ORDER BY 2 DESC;

⤢ expand

sql

-- Duplicate check — returns no rows, confirming clean data
SELECT step, txn_type, amount, name_orig, oldbalance_org, newbalance_orig,
       name_dest, oldbalance_dest, newbalance_dest, is_fraud, is_flagged_fraud
FROM payments_transactions
GROUP BY step, txn_type, amount, name_orig, oldbalance_org, newbalance_orig,
         name_dest, oldbalance_dest, newbalance_dest, is_fraud, is_flagged_fraud
HAVING count(*) > 1;

⤢ expand

No rows returned — the dataset contains no duplicate records.

02 — Where Does Fraud Happen?

Breaking fraud down by transaction type reveals which channels are targeted. For each type the query computes the transaction count, the number and rate of frauds, and the single biggest fraudulent amount.

sql

SELECT txn_type,
       TO_CHAR(count(txn_type),'FM9,999,999') AS Nr_of_transactions,
       (SELECT TO_CHAR(count(txn_type), 'FM9,999,999')
        FROM payments_transactions f
        WHERE f.is_fraud > 0 AND f.txn_type = p.txn_type) AS Fraud_Transactions,
       ROUND((SELECT count(txn_type)
        FROM payments_transactions f
        WHERE f.is_fraud > 0 AND f.txn_type = p.txn_type) / count(txn_type), 4) AS Fraud_Rate,
       (SELECT TO_CHAR(max(amount),'FM9,999,999,999')
        FROM payments_transactions g
        WHERE g.is_fraud > 0 AND g.txn_type = p.txn_type) AS Biggest_Fraud
FROM payments_transactions p
GROUP BY txn_type
ORDER BY 3 DESC;

⤢ expand

Fraud occurs only in TRANSFER and CASH_OUT. TRANSFER has the highest fraud rate (0.77%), and both types share the same £10M ceiling on the largest fraud.

03 — How Much? Fraud Value & Amount Patterns

sql

-- Total money moved vs total lost to fraud
SELECT
  (SELECT TO_CHAR(SUM(amount), 'FM9,999,999,999,999') FROM payments_transactions) AS Total_Amount,
  (SELECT TO_CHAR(SUM(amount), 'FM9,999,999,999,999') FROM payments_transactions
   WHERE is_fraud <> 0) AS Fraud_Amount
FROM dual;

⤢ expand

sql

-- Average amount: overall vs legitimate vs fraudulent
SELECT
  (SELECT TO_CHAR(avg(amount), 'FM9,999,999,999') FROM payments_transactions) AS Total_Avg,
  (SELECT TO_CHAR(avg(amount), 'FM9,999,999,999') FROM payments_transactions
   WHERE is_fraud = 0) AS Legitimate_Avg,
  (SELECT TO_CHAR(avg(amount), 'FM9,999,999,999') FROM payments_transactions
   WHERE is_fraud <> 0) AS Fraud_Avg
FROM dual;

⤢ expand

Fraudulent transactions average £1.47M versus £178K for legitimate ones — fraud targets high-value transfers.

sql

-- Do larger transactions have a higher fraud rate?
SELECT amount_bucket, count(*) AS number_of_transactions,
       SUM(is_fraud) AS fraudulent_transactions
FROM (
    SELECT is_fraud,
           CASE WHEN amount > 200000 THEN 'large tx'
                WHEN amount > 100000 THEN 'medium tx'
                ELSE 'small tx'
           END AS amount_bucket
    FROM payments_transactions
)
GROUP BY amount_bucket;

⤢ expand

Large transactions (>£200K) carry far more fraud (5,471 cases) than medium or small, confirming fraud skews toward big amounts.

04 — When Does Fraud Happen?

The step column counts hours since the start of the simulation. Using MOD(step, 24) maps each transaction to an hour of the day to reveal volume and fraud patterns across a 24-hour cycle.

sql

-- Transaction volume and fraud count by hour of day
SELECT count(*) AS Transaction_volume,
       TO_CHAR(MOD(step,24) || ':00') AS Time_of_day,
       SUM(is_fraud) AS Fraudulent_transactions
FROM payments_transactions
GROUP BY MOD(step,24)
ORDER BY 1 DESC;

⤢ expand

Transaction volume peaks during daytime hours, but fraud stays remarkably constant (≈300–375 per hour) around the clock — fraud does not follow normal user activity.

05 — Behavioural Patterns & Detection Quality

sql

-- Do frauds often drain the account to zero?
SELECT
  (SELECT count(*) FROM payments_transactions WHERE is_fraud = 1) AS Fraudulent_transactions,
  (SELECT count(*) FROM payments_transactions
   WHERE is_fraud = 1 AND newbalance_orig = 0) AS Drained_to_zero
FROM dual;

⤢ expand

8,053 of 8,213 frauds (98%) emptied the account completely — a strong behavioural signal for detection.

sql

-- Balance inconsistencies: sender balance doesn't drop by the amount sent
SELECT -amount AS "Transaction Amount",
       newbalance_orig - oldbalance_org AS "Transaction Difference"
FROM payments_transactions
WHERE newbalance_orig - oldbalance_org <> -amount;

⤢ expand

Many transactions show a mismatch between the amount sent and the actual balance change — another red flag worth modelling.

sql

-- How effective is the existing fraud flag?
SELECT
  (SELECT count(is_fraud) FROM payments_transactions
   WHERE is_fraud <> 0) AS "Fraudulent Transactions",
  (SELECT count(is_flagged_fraud) FROM payments_transactions
   WHERE is_flagged_fraud <> 0) AS "Flagged Fraudulent Transactions",
  ROUND((SELECT count(is_flagged_fraud) FROM payments_transactions
         WHERE is_flagged_fraud <> 0) /
        (SELECT count(is_fraud) FROM payments_transactions
         WHERE is_fraud <> 0), 5) AS "Flag Success Rate"
FROM dual;

The built-in flag catches only a tiny fraction of real frauds — showing why a data-driven detection approach (using the patterns above) would add real value.